package com.big.codegen.util;

import com.big.codegen.domain.dto.ColumnDTO;
import com.big.codegen.domain.dto.DatasourceDTO;
import com.big.common.core.exception.CustomerException;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.StringUtils;

import java.util.List;
import java.util.stream.Collectors;

/**
 * SQL生成器工具类
 * 支持多种数据库的DDL语句生成
 *
 * @author 山归岿
 * @create 2025-01-03
 */
@Slf4j
public class SqlGeneratorUtils {

    /**
     * 生成创建表的SQL语句
     *
     * @param datasource 数据源配置
     * @param tableName  表名
     * @param tableComment 表注释
     * @param columns    字段列表
     * @return 创建表的SQL语句
     */
    public static String generateCreateTableSql(DatasourceDTO datasource, String tableName, String tableComment, List<ColumnDTO> columns) {
        if (columns == null || columns.isEmpty()) {
            throw new CustomerException("字段列表不能为空");
        }

        DatabaseUtils.DatabaseType dbType = DatabaseUtils.DatabaseType.fromType(datasource.getDbType());
        StringBuilder sql = new StringBuilder();

        switch (dbType) {
            case MYSQL:
                sql.append(generateMySqlCreateTable(tableName, tableComment, columns));
                break;
            case POSTGRESQL:
                sql.append(generatePostgreSqlCreateTable(tableName, tableComment, columns));
                break;
            case ORACLE:
                sql.append(generateOracleCreateTable(tableName, tableComment, columns));
                break;
            case SQLSERVER:
                sql.append(generateSqlServerCreateTable(tableName, tableComment, columns));
                break;
            case H2:
                sql.append(generateH2CreateTable(tableName, tableComment, columns));
                break;
            default:
                throw new CustomerException("不支持的数据库类型: " + datasource.getDbType());
        }

        return sql.toString();
    }

    /**
     * 生成MySQL创建表SQL
     */
    private static String generateMySqlCreateTable(String tableName, String tableComment, List<ColumnDTO> columns) {
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE `").append(tableName).append("` (\n");

        // 生成字段定义
        for (int i = 0; i < columns.size(); i++) {
            ColumnDTO column = columns.get(i);
            sql.append("  `").append(column.getColumnName()).append("` ");
            sql.append(getMySqlColumnType(column));
            
            // 是否允许为空
            if (!column.getIsNullable()) {
                sql.append(" NOT NULL");
            }
            
            // 自增
            if (column.getIsAutoIncrement()) {
                sql.append(" AUTO_INCREMENT");
            }
            
            // 默认值
            if (StringUtils.hasText(column.getColumnDefault())) {
                sql.append(" DEFAULT ").append(column.getColumnDefault());
            }
            
            // 字段注释
            if (StringUtils.hasText(column.getColumnComment())) {
                sql.append(" COMMENT '").append(column.getColumnComment()).append("'");
            }
            
            if (i < columns.size() - 1) {
                sql.append(",");
            }
            sql.append("\n");
        }

        // 主键
        List<String> primaryKeys = columns.stream()
                .filter(ColumnDTO::getIsPrimaryKey)
                .map(ColumnDTO::getColumnName)
                .toList();
        
        if (!primaryKeys.isEmpty()) {
            sql.append(",\n  PRIMARY KEY (");
            sql.append(primaryKeys.stream().map(key -> "`" + key + "`").collect(Collectors.joining(", ")));
            sql.append(")\n");
        }

        sql.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        
        if (StringUtils.hasText(tableComment)) {
            sql.append(" COMMENT='").append(tableComment).append("'");
        }
        
        sql.append(";");
        return sql.toString();
    }

    /**
     * 生成PostgreSQL创建表SQL
     */
    private static String generatePostgreSqlCreateTable(String tableName, String tableComment, List<ColumnDTO> columns) {
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE \"").append(tableName).append("\" (\n");

        // 生成字段定义
        for (int i = 0; i < columns.size(); i++) {
            ColumnDTO column = columns.get(i);
            sql.append("  \"").append(column.getColumnName()).append("\" ");
            sql.append(getPostgreSqlColumnType(column));
            
            // 是否允许为空
            if (!column.getIsNullable()) {
                sql.append(" NOT NULL");
            }
            
            // 默认值
            if (StringUtils.hasText(column.getColumnDefault())) {
                sql.append(" DEFAULT ").append(column.getColumnDefault());
            }
            
            if (i < columns.size() - 1) {
                sql.append(",");
            }
            sql.append("\n");
        }

        // 主键
        List<String> primaryKeys = columns.stream()
                .filter(ColumnDTO::getIsPrimaryKey)
                .map(ColumnDTO::getColumnName)
                .toList();
        
        if (!primaryKeys.isEmpty()) {
            sql.append(",\n  PRIMARY KEY (");
            sql.append(primaryKeys.stream().map(key -> "\"" + key + "\"").collect(Collectors.joining(", ")));
            sql.append(")\n");
        }

        sql.append(");");
        
        // 表注释
        if (StringUtils.hasText(tableComment)) {
            sql.append("\nCOMMENT ON TABLE \"").append(tableName).append("\" IS '").append(tableComment).append("';");
        }
        
        // 字段注释
        for (ColumnDTO column : columns) {
            if (StringUtils.hasText(column.getColumnComment())) {
                sql.append("\nCOMMENT ON COLUMN \"").append(tableName).append("\".\"").append(column.getColumnName())
                   .append("\" IS '").append(column.getColumnComment()).append("';");
            }
        }
        
        return sql.toString();
    }

    /**
     * 生成Oracle创建表SQL
     */
    private static String generateOracleCreateTable(String tableName, String tableComment, List<ColumnDTO> columns) {
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE ").append(tableName.toUpperCase()).append(" (\n");

        // 生成字段定义
        for (int i = 0; i < columns.size(); i++) {
            ColumnDTO column = columns.get(i);
            sql.append("  ").append(column.getColumnName().toUpperCase()).append(" ");
            sql.append(getOracleColumnType(column));
            
            // 是否允许为空
            if (!column.getIsNullable()) {
                sql.append(" NOT NULL");
            }
            
            // 默认值
            if (StringUtils.hasText(column.getColumnDefault())) {
                sql.append(" DEFAULT ").append(column.getColumnDefault());
            }
            
            if (i < columns.size() - 1) {
                sql.append(",");
            }
            sql.append("\n");
        }

        // 主键
        List<String> primaryKeys = columns.stream()
                .filter(ColumnDTO::getIsPrimaryKey)
                .map(column -> column.getColumnName().toUpperCase())
                .collect(Collectors.toList());
        
        if (!primaryKeys.isEmpty()) {
            sql.append(",\n  PRIMARY KEY (");
            sql.append(String.join(", ", primaryKeys));
            sql.append(")\n");
        }

        sql.append(");");
        
        // 表注释
        if (StringUtils.hasText(tableComment)) {
            sql.append("\nCOMMENT ON TABLE ").append(tableName.toUpperCase()).append(" IS '").append(tableComment).append("';");
        }
        
        // 字段注释
        for (ColumnDTO column : columns) {
            if (StringUtils.hasText(column.getColumnComment())) {
                sql.append("\nCOMMENT ON COLUMN ").append(tableName.toUpperCase()).append(".").append(column.getColumnName().toUpperCase())
                   .append(" IS '").append(column.getColumnComment()).append("';");
            }
        }
        
        return sql.toString();
    }

    /**
     * 生成SQL Server创建表SQL
     */
    private static String generateSqlServerCreateTable(String tableName, String tableComment, List<ColumnDTO> columns) {
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE [").append(tableName).append("] (\n");

        // 生成字段定义
        for (int i = 0; i < columns.size(); i++) {
            ColumnDTO column = columns.get(i);
            sql.append("  [").append(column.getColumnName()).append("] ");
            sql.append(getSqlServerColumnType(column));
            
            // 自增
            if (column.getIsAutoIncrement()) {
                sql.append(" IDENTITY(1,1)");
            }
            
            // 是否允许为空
            if (!column.getIsNullable()) {
                sql.append(" NOT NULL");
            }
            
            // 默认值
            if (StringUtils.hasText(column.getColumnDefault())) {
                sql.append(" DEFAULT ").append(column.getColumnDefault());
            }
            
            if (i < columns.size() - 1) {
                sql.append(",");
            }
            sql.append("\n");
        }

        // 主键
        List<String> primaryKeys = columns.stream()
                .filter(ColumnDTO::getIsPrimaryKey)
                .map(ColumnDTO::getColumnName)
                .toList();
        
        if (!primaryKeys.isEmpty()) {
            sql.append(",\n  PRIMARY KEY (");
            sql.append(primaryKeys.stream().map(key -> "[" + key + "]").collect(Collectors.joining(", ")));
            sql.append(")\n");
        }

        sql.append(");");
        return sql.toString();
    }

    /**
     * 生成H2创建表SQL
     */
    private static String generateH2CreateTable(String tableName, String tableComment, List<ColumnDTO> columns) {
        // H2使用类似MySQL的语法
        return generateMySqlCreateTable(tableName, tableComment, columns);
    }

    /**
     * 获取MySQL字段类型
     */
    private static String getMySqlColumnType(ColumnDTO column) {
        String type = column.getColumnType().toUpperCase();
        Integer length = column.getColumnLength();
        Integer precision = column.getColumnPrecision();
        
        switch (type) {
            case "VARCHAR":
            case "CHAR":
                return type + "(" + (length != null ? length : 255) + ")";
            case "INT":
            case "INTEGER":
                return "INT" + (length != null ? "(" + length + ")" : "");
            case "BIGINT":
                return "BIGINT" + (length != null ? "(" + length + ")" : "");
            case "DECIMAL":
            case "NUMERIC":
                if (length != null && precision != null) {
                    return "DECIMAL(" + length + "," + precision + ")";
                } else if (length != null) {
                    return "DECIMAL(" + length + ",0)";
                } else {
                    return "DECIMAL(10,2)";
                }
            default:
                return type;
        }
    }

    /**
     * 获取PostgreSQL字段类型
     */
    private static String getPostgreSqlColumnType(ColumnDTO column) {
        String type = column.getColumnType().toUpperCase();
        Integer length = column.getColumnLength();
        Integer precision = column.getColumnPrecision();
        
        switch (type) {
            case "VARCHAR":
                return "VARCHAR(" + (length != null ? length : 255) + ")";
            case "CHAR":
                return "CHAR(" + (length != null ? length : 1) + ")";
            case "TEXT":
                return "TEXT";
            case "INT":
            case "INTEGER":
                return "INTEGER";
            case "BIGINT":
                return "BIGINT";
            case "DECIMAL":
            case "NUMERIC":
                if (length != null && precision != null) {
                    return "NUMERIC(" + length + "," + precision + ")";
                } else {
                    return "NUMERIC";
                }
            case "DATETIME":
            case "TIMESTAMP":
                return "TIMESTAMP";
            case "DATE":
                return "DATE";
            case "TIME":
                return "TIME";
            default:
                return type;
        }
    }

    /**
     * 获取Oracle字段类型
     */
    private static String getOracleColumnType(ColumnDTO column) {
        String type = column.getColumnType().toUpperCase();
        Integer length = column.getColumnLength();
        Integer precision = column.getColumnPrecision();
        
        switch (type) {
            case "VARCHAR":
            case "VARCHAR2":
                return "VARCHAR2(" + (length != null ? length : 255) + ")";
            case "CHAR":
                return "CHAR(" + (length != null ? length : 1) + ")";
            case "TEXT":
            case "CLOB":
                return "CLOB";
            case "INT":
            case "INTEGER":
                return "NUMBER(10)";
            case "BIGINT":
                return "NUMBER(19)";
            case "DECIMAL":
            case "NUMBER":
                if (length != null && precision != null) {
                    return "NUMBER(" + length + "," + precision + ")";
                } else if (length != null) {
                    return "NUMBER(" + length + ")";
                } else {
                    return "NUMBER";
                }
            case "DATETIME":
            case "TIMESTAMP":
                return "TIMESTAMP";
            case "DATE":
                return "DATE";
            default:
                return type;
        }
    }

    /**
     * 获取SQL Server字段类型
     */
    private static String getSqlServerColumnType(ColumnDTO column) {
        String type = column.getColumnType().toUpperCase();
        Integer length = column.getColumnLength();
        Integer precision = column.getColumnPrecision();
        
        switch (type) {
            case "VARCHAR":
                return "VARCHAR(" + (length != null ? length : 255) + ")";
            case "CHAR":
                return "CHAR(" + (length != null ? length : 1) + ")";
            case "TEXT":
                return "TEXT";
            case "INT":
            case "INTEGER":
                return "INT";
            case "BIGINT":
                return "BIGINT";
            case "DECIMAL":
            case "NUMERIC":
                if (length != null && precision != null) {
                    return "DECIMAL(" + length + "," + precision + ")";
                } else {
                    return "DECIMAL(18,2)";
                }
            case "DATETIME":
                return "DATETIME";
            case "TIMESTAMP":
                return "DATETIME2";
            case "DATE":
                return "DATE";
            case "TIME":
                return "TIME";
            default:
                return type;
        }
    }
}